﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using RFIDStockManagement.Model.Public;
using RFIDStockManagement.Common.Public;
using RFIDStockManagement.Common.Public.Enums;
using System.Data.Common;
using RFIDStockManagement.IDAL;

namespace RFIDStockManagement.DAL
{
    public class StockCheckAcess:IStockCheck
    {
        private DbHelper _db;
        private const string SELECT_CHECKSTOCKINFO = @"SELECT t1.FCaption,t1.Fid,t1.FStartTime,t1.FEndTime,t1.FStatus,t1.FNote 
From RFID_Stock_Check t1";
        private const string INSERT_CHECKSTOCKINFO = @"Insert Into RFID_Stock_Check(FCaption,FStartTime,FNote,FStatus)Values({0})";

        private const string CLEARTMPTABLE_SQL = @"IF object_id('tempdb..{0}') is not null
Begin
	drop table {0}
end ";
        private const string DELETE_UNNESSERYDATAS = @"Delete t1 From {0} t1 Left Join (Select Max(Fid) as Fid From {0} Group by FRFID ) t2 on t1.Fid= t2.Fid Where t2.Fid is null";

        private const string INSERT_STOCK_CHECK_DETAIL = @"Insert Into RFID_Stock_Check_Detail(FID,FItemId,FRFID,FStockId,FStockPlaceId,FUnitId)
Select {0},FItemID,FRFID,FStockId,FStockPlaceId,FUnitId From {1} t1 Where not Exists(select 1 from RFID_Stock_Check_Detail tt1 Where tt1.fid ={0} And tt1.FRFID = t1.FRFID)";

        public StockCheckAcess()
        {
            _db = new DbHelper();
        }
        
        private StockCheckInfo GetStockCheckInfoFromDataRow(DataRow infos)
        {
            StockCheckInfo retVal = new StockCheckInfo();

            retVal.FId = PublicFunction.NotNullInt(infos["Fid"]);
            retVal.FStartTime = PublicFunction.NotNullDateTime(infos["FStartTime"]);
            retVal.FEndTime = PublicFunction.NotNullDateTime(infos["FEndTime"]);
            retVal.FCaption = PublicFunction.NotNull(infos["FCaption"]);
            retVal.FNote = PublicFunction.NotNull(infos["Fnote"]);
            retVal.Status = (EnumStockCheckStatus)PublicFunction.NotNullInt(infos["FStatus"]);
            return retVal;
        }

        private DataTable GetTableBySQL(string sql)
        {
            DbCommand cmd = _db.GetSqlStringCommand(sql);

            return _db.ExecuteDataTable(cmd);
        }


        public StockCheckInfo GetStockCheckMainInfoById(int id)
        {
            string sql = SELECT_CHECKSTOCKINFO + " Where t1.Fid = " + id.ToString();

            DataTable infos = GetTableBySQL(sql);

            if (infos.Rows.Count > 0)
            {
                return GetStockCheckInfoFromDataRow(infos.Rows[0]);
            }
            return null;
        }

        public bool Delete(int id, out string errString)
        {
            errString = string.Empty;
            string sql = "Delete From RFID_Stock_Check Where Fid ={0} \r\n Delete From RFID_Stock_Check_Detail Where Fid={0}";
            try
            {
                DbCommand cmd = _db.GetSqlStringCommand(string.Format(sql,id.ToString()));
                _db.ExecuteNonQuery(cmd);
            }
            catch(Exception ex)
            {
                errString = ex.Message;
                return false;
            }
            return true;
        }

        public StockCheckInfo GetStockCheckMainInfo(int status)
        {
            string sql = @"SELECT top 1 t1.Fid,t1.FStartTime,t1.FEndTime,t1.FStatus,t1.FNote,t2.FName,t2.FNumber 
From RFID_Stock_Check t1";
            if (status != 0)
            {
                sql += "Where t1.FStatus = " + status.ToString();
            }
            sql += "\r\n order by FStartTime Desc";

            DataTable infos = GetTableBySQL(sql);

            if (infos.Rows.Count > 0)
            {
                return GetStockCheckInfoFromDataRow(infos.Rows[0]);
            }
            return null;
        }

        public int SaveStockCheckHeadInfo(StockCheckInfo headInfo)
        {
            int fid = 0;
            string sql = "";
            sql += PublicFunction.AddToStringWithCom(headInfo.FCaption);
            sql += PublicFunction.AddToStringWithCom(headInfo.FStartTime.ToString(PublicConst.LongTimeFormater));
            sql += PublicFunction.AddToStringWithCom(headInfo.FNote);
            sql += ((int)headInfo.Status).ToString();
            sql = string.Format("Set nocount on \r\n"+ INSERT_CHECKSTOCKINFO, sql);

            sql += "\r\n Select @@Identity as Fid";

            DataTable curFid = GetTableBySQL(sql);

            if (curFid.Rows.Count > 0)
            {
                fid = PublicFunction.NotNullInt(curFid.Rows[0]["Fid"]);
            }

            return fid;
        }


        public bool SaveItemDetailInfo(int fid, List<StockCheckDetail> infos)
        {
            StringBuilder sbSql;
            string tmpTableName = "#TmpStockCheckIn_" + Guid.NewGuid().ToString();

            tmpTableName = tmpTableName.Replace("-", "_");
            int len = infos.Count;

            if (len == 0) return true;

            sbSql = new StringBuilder();
            sbSql.AppendLine("Create Table " + tmpTableName + "(FID Int Identity(1,1), FRFID nvarchar(200),");
            sbSql.AppendLine("FItemID BigInt not null default(0),FUnitId bigint default(0),");
            sbSql.AppendLine("FStockPlaceId bigint not null Default(0),FStockId bigint not null default(0))");


            for (int i = 0; i < len; i++)
            {
                if (i % 80 == 0)
                {
                    sbSql.AppendLine("");
                    sbSql.Append("Insert Into ");
                    sbSql.Append(tmpTableName);
                    sbSql.AppendLine("(FRFID,FItemID,FUnitId,FStockId,FStockPlaceId)");
                    sbSql.Append("Select ");
                    sbSql.Append(PublicFunction.AddToStringWithCom(infos[i].FRfid));
                    sbSql.Append(PublicFunction.AddToValueWithCom(infos[i].ItemId));
                    sbSql.Append(PublicFunction.AddToValueWithCom(infos[i].FUnitId));
                    sbSql.Append(PublicFunction.AddToValueWithCom(infos[i].FStockId));
                    sbSql.AppendLine(infos[i].StockPlaceId.ToString());
                }
                else
                {
                    sbSql.Append("Union All Select ");
                    sbSql.Append(PublicFunction.AddToStringWithCom(infos[i].FRfid));
                    sbSql.Append(PublicFunction.AddToValueWithCom(infos[i].ItemId));
                    sbSql.Append(PublicFunction.AddToValueWithCom(infos[i].FUnitId));
                    sbSql.Append(PublicFunction.AddToValueWithCom(infos[i].FStockId));
                    sbSql.AppendLine(infos[i].StockPlaceId.ToString());
                }
            }

            //这里要先处理当前重复数据
            sbSql.AppendLine(string.Format(DELETE_UNNESSERYDATAS,tmpTableName));

            //这里插入盘点表中没有的数据

            sbSql.AppendLine(string.Format(INSERT_STOCK_CHECK_DETAIL, fid.ToString(), tmpTableName));


            DbCommand cmd = _db.GetSqlStringCommand(sbSql.ToString());

            try
            {
                _db.ExecuteNonQuery(cmd);
            }
            catch
            {
                return false;
            }
            finally
            {
                cmd = _db.GetSqlStringCommand(string.Format(CLEARTMPTABLE_SQL, tmpTableName));
                _db.ExecuteNonQuery(cmd);
            }

            return true;

        }

        public bool UpdateStatus(int id, int status)
        {
            string endTime = "null";
            if (status == 3) //结束
            {
                endTime = "GetDate()";
            }
            string sql = "Update RFID_Stock_Check Set FStatus = {0},FEndTime={2} Where Fid={1}";

            DbCommand cmd = _db.GetSqlStringCommand(string.Format(sql,status,id,endTime));
            _db.ExecuteNonQuery(cmd);

            return true;

        }

        public List<StockCheckInfo> GetStockCheckList(int status)
        {
            List<StockCheckInfo> retVals = new List<StockCheckInfo>();
            StockCheckInfo newInfo;
            string sql = SELECT_CHECKSTOCKINFO;
            if (status != 0)
            {
                sql += "Where t1.FStatus = " + status.ToString();
            }
            sql += "\r\n order by FStartTime Desc";

            DataTable infos = GetTableBySQL(sql);

            foreach (DataRow x in infos.Rows)
            {
                newInfo = GetStockCheckInfoFromDataRow(x);
                retVals.Add(newInfo);
            }

            return retVals;
        }

      
    }
}
